

package trabalhotec.BD;

import java.sql.*;
import java.util.Vector;

public class pesquisa {
    Connection con;
    Statement stmt;
    public Vector<Vector> listaPesq(String origem,String destino,Timestamp data){
        Vector<Vector> pesq = new Vector<Vector>();
        String proc = "";
        con = null;
        stmt = null;
        Statement stmt2 = null;
        try {
            con = Conexao.abrirConexao();
            stmt = con.createStatement();
            stmt2 = con.createStatement();
            if((origem==null)&& destino==null){
                proc = "Select * from voos where Datadepartida = '"+data+"'";
            }
            if(origem==null){
                if(data==null)proc = "select * from voos where Destino = '"+destino+"'";
                else proc = "Select * from voos where Destino = '"+destino+"' AND Datadepartida = '"+data+"'";
            }
            if(destino==null){
                if(data==null)proc = "select * from voos where Origem = '"+origem+"'";
                else proc = "Select * from voos where Origem = '"+origem+"' AND Datadepartida = '"+data+"'";
            }
            if(data == null){
                proc = "Select * from voos where Destino = '"+destino+"' AND Origem = '"+origem+"'";
            }
            ResultSet rs = stmt.executeQuery(proc);
            while(rs.next()){
                Vector list = new Vector();
                list.add(rs.getString("Codigo"));
                list.add(rs.getString("Origem"));
                list.add(rs.getString("Destino"));
                list.add(rs.getTimestamp("Datadepartida"));
                list.add(rs.getTimestamp("Datadechegada"));
                list.add(rs.getLong("Tempodevoo"));
                float preco = rs.getFloat("Preco");
                String buscapromo = "Select * from promocao where Voo = '"+rs.getString("Codigo")+"'";
                ResultSet rs2 = stmt2.executeQuery(buscapromo);
                if (rs2.next()) preco = preco - (rs2.getFloat("Desconto")/100)*preco;
                list.add(preco);
                list.add(rs.getInt("Quantidadedelugares"));
                pesq.add(list);
            }
        } catch (SQLException ex) {
            System.out.println("Erro ao acessar Banco de dados");
        }finally{
            try {
                stmt.close();
                stmt2.close();
                con.close();
            } catch (SQLException ex) {
                System.out.println("Erro ao fechar conexao com banco de dados");
            }
        }
        return pesq;
    }
    public Vector<Vector> buscaAero(String aero,Timestamp dataini,Timestamp datafim){
        Vector<Vector> aeros = new Vector<Vector>();
        con = null;
        stmt = null;
        Statement stmt2 = null;
        try {
            con = Conexao.abrirConexao();
            stmt = con.createStatement();
            stmt2 = con.createStatement();
            String[] aero2 = aero.split(("-"));
            String buscar = "SELECT * FROM voos WHERE (Origem = '"+aero2[0]+"' OR Destino = '"+aero2[0]+"') AND (Datadepartida > '"+dataini+"' AND Datadechegada < '"+datafim+"')";
            ResultSet rs = stmt.executeQuery(buscar);
            while(rs.next()){
                Vector list = new Vector();
                list.add(rs.getString("Codigo"));
                list.add(rs.getString("Origem"));
                list.add(rs.getString("Destino"));
                list.add(rs.getTimestamp("Datadepartida").toString());
                list.add(rs.getTimestamp("Datadechegada").toString());
                list.add(""+rs.getLong("Tempodevoo"));
                float preco = rs.getFloat("Preco");
                String buscapromo = "Select * from promocao where Voo = '"+rs.getString("Codigo")+"'";
                ResultSet rs2 = stmt2.executeQuery(buscapromo);
                if (rs2.next()) preco = preco - (rs2.getFloat("Desconto")/100)*preco;
                list.add(preco);
                list.add(""+rs.getInt("Quantidadedelugares"));
                aeros.add(list);
            }
        } catch (SQLException ex) {
            System.out.println("erro ao acessar banco de dados");
        } finally{
            try {
                stmt.close();
                stmt2.close();
                con.close();
            } catch (SQLException ex) {
                System.out.println("erro ao fechar conexao");
            }
        }
        return aeros;
    } 
    public Vector pesqResult(String cod){
        Vector list = new Vector();
        con = null;
        stmt = null;
        try {
            con = Conexao.abrirConexao();
            stmt = con.createStatement();
            String procurar = "Select * from voos where Codigo = '"+cod+"'";
            ResultSet rs = stmt.executeQuery(procurar);
            if(rs.next()){
                list.add(rs.getString("Codigo"));
                list.add(rs.getString("Origem"));
                list.add(rs.getString("Destino"));
                list.add(rs.getTimestamp("Datadepartida"));
                list.add(rs.getInt("Quantidadedelugares"));
            }
        } catch (SQLException ex) {
            System.out.println("erro ao acessar banco de dados");
        }finally{
            try {
                stmt.close();
                con.close();
            } catch (SQLException ex) {
                System.out.println("erro ao fechar conexao");
            }
        }
        return list;
    }
    public Vector<Vector<String>> vooClientes(String cod){
        Vector<Vector<String>> clientes = new Vector<Vector<String>>();
        con = null;
        stmt = null;
        Statement stmt2 = null;
        String cpf = null;
        try {
            con = Conexao.abrirConexao();
            stmt = con.createStatement();
            stmt2 = con.createStatement();
            String buscarclientes = "Select * from passagens where Cod_Voo = '"+cod+"'";
            ResultSet rs = stmt.executeQuery(buscarclientes);
            ResultSet rs2;
            while(rs.next()){
                Vector<String> list = new Vector<String>();
                cpf = rs.getString("codCli");
                String detalhesclientes = "Select * from cliente where CPF = '"+cpf+"'";
                rs2 = stmt2.executeQuery(detalhesclientes);
                if(rs2.next()){
                    list.add(rs2.getString("Nome"));
                    list.add(rs2.getString("CPF"));
                    list.add(rs2.getString("Email"));
                    list.add(rs2.getString("Sexo"));
                    clientes.add(list);
                }
            }
                
        } catch (SQLException ex) {
            System.out.println("erro ao acessar o banco de dados");
        }finally{
            try {
                stmt.close();
                stmt2.close();
                con.close();
            } catch (SQLException ex) {
                System.out.println("erro ao fechra conexao");
            }
        }
        return clientes;
    }
    public Vector<Vector> busctrecho(String aero1,String aero2, Timestamp datapart,Timestamp datacheg){
        Vector<Vector> bus = new Vector<Vector>();
        con = null;
        stmt = null;
        Statement stmt2 = null;
        try {
            con = Conexao.abrirConexao();
            stmt= con.createStatement();
            stmt2 = con.createStatement();
            String[] aeroo = aero1.split("-");
            String[] aerod = aero2.split("-");
            String busca = "Select * from voos where Origem = '"+aeroo[0]+"' AND Destino = '"+aerod[0]+"' AND (Datadepartida > '"+datapart+"' AND Datadechegada < '"+datacheg+"')";
            ResultSet rs = stmt.executeQuery(busca);
            while(rs.next()){
                Vector list = new Vector();
                list.add(rs.getString("Codigo"));
                list.add(rs.getString("Origem"));
                list.add(rs.getString("Destino"));
                list.add(DataUtil.obterDataDoBanco(rs.getTimestamp("Datadepartida")));
                list.add(DataUtil.obterDataDoBanco(rs.getTimestamp("Datadechegada")));
                float preco = rs.getFloat("Preco");
                String buscapromo = "Select * from promocao where Voo = '"+rs.getString("Codigo")+"'";
                ResultSet rs2 = stmt2.executeQuery(buscapromo);
                if (rs2.next()) preco = preco - (rs2.getFloat("Desconto")/100)*preco;
                list.add(preco);
                list.add(rs.getInt("Quantidadedelugares"));
                bus.add(list);
            }
        } catch (SQLException ex) {
            System.out.println("Erro ao acessar o banco de dados");
        }finally{
            try {
                stmt.close();
                stmt2.close();
                con.close();
            } catch (SQLException ex) {
                System.out.println("erro ao fechar conexao");
            }
        }
        return bus;   
    }
    public void compra(String cod,String cpf){
        con = null;
        stmt = null;
        try {
            con = Conexao.abrirConexao();
            stmt = con.createStatement();
            String inserir = "Insert into passagens values ('"+cod+"','"+cpf+"')";
            stmt.executeUpdate(inserir);
            String decre = "UPDATE voos SET Quantidadedelugares = Quantidadedelugares - 1 WHERE Codigo = '"+cod+"'";
            stmt.executeUpdate(decre);
        } catch (SQLException ex) {
            System.out.println("Erro ao acessar banco de dados");
        }finally{
            try {
                stmt.close();
                con.close();
            } catch (SQLException ex) {
                System.out.println("Erro ao fechar conexao");
            }
        }
    }
    public Vector<Vector> buscavol(String origem,String destino,Timestamp data){
        Vector<Vector> busca = new Vector<Vector>();
        con = null;
        stmt = null;
        Statement stmt2 = null;
        try {
            con = Conexao.abrirConexao();
            stmt = con.createStatement();
            stmt2 = con.createStatement();
            String [] o = origem.split("-");
            String [] d = destino.split("-");
            String buscar = "Select * from voos where Origem = '"+o[0]+"' And Destino = '" + d[0]+"' and Datadechegada > '"+data+"'";
            ResultSet rs = stmt.executeQuery(buscar);
            while(rs.next()){
                Vector list = new Vector();
                list.add(rs.getString("Codigo"));
                list.add(rs.getString("Origem"));
                list.add(rs.getString("Destino"));
                list.add(DataUtil.obterDataDoBanco(rs.getTimestamp("Datadepartida")).toString());
                list.add(DataUtil.obterDataDoBanco(rs.getTimestamp("Datadechegada")).toString());
                float preco = rs.getFloat("Preco");
                String buscapromo = "Select * from promocao where Voo = '"+rs.getString("Codigo")+"'";
                ResultSet rs2 = stmt2.executeQuery(buscapromo);
                if (rs2.next()) preco = preco - (rs2.getFloat("Desconto")/100)*preco;
                list.add(preco);
                list.add(rs.getInt("Quantidadedelugares"));
                busca.add(list);
            }
        } catch (SQLException ex) {
            System.out.println("porra ao acessar banco de dados");
        }finally{
            try {
                stmt.close();
                stmt2.close();
                con.close();
            } catch (SQLException ex) {
                System.out.println("erro ao fechar a conexao com banco de dados");
            }
        }
        return busca;
    }
   
}
